package com.csu.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Date;

import com.csu.bean.Outlay;
import com.csu.db.DBUtils;

public class OutlayDaoImpl implements OutlayDao{


	public List<Outlay> getOutlayByPage(int index, int pageSize) {
		List<Outlay> list = new ArrayList<>();
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT * FROM outlay LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, index);
			ps.setInt(2, pageSize);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			while(rs.next()){
				Outlay outlay = new Outlay();
				outlay.setId(rs.getInt(1));//取第一个字段的值\
				outlay.setOutMoney(rs.getBigDecimal(2));
				outlay.setOutType(rs.getString(3));
				outlay.setOutPeople(rs.getString(4));
				outlay.setOutDate(rs.getDate(5));
				outlay.setOutUse(rs.getString(6));
				outlay.setNotes(rs.getString(7));
				list.add(outlay);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		
		return list;
	}
	
	@Override
	public List<Outlay> getOutlayByType(String outlayType) {
		// TODO Auto-generated method stub
		List<Outlay> list = new ArrayList<>();
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT * FROM outlay WHERE 支出类型=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, outlayType);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			while(rs.next()){
				Outlay outlay = new Outlay();
				outlay.setId(rs.getInt(1));//取第一个字段的值\
				outlay.setOutMoney(rs.getBigDecimal(2));
				outlay.setOutType(rs.getString(3));
				outlay.setOutPeople(rs.getString(4));
				outlay.setOutDate(rs.getDate(5));
				outlay.setOutUse(rs.getString(6));
				outlay.setNotes(rs.getString(7));
				list.add(outlay);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		
		return list;
	}

	@Override
	public int getOutlayCount() {
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT COUNT(*) FROM outlay";
			ps = conn.prepareStatement(sql);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		return 0;
	}
	
	@Override
	public int getOutlayCountByType(String outlayType) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT COUNT(*) FROM outlay WHERE 支出类型=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, outlayType);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public int addOutlay(double outMoney, String outType, String outPeople, String outDate, String outUse,
			String notes) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "INSERT INTO outlay VALUES(null,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setDouble(1, outMoney);//将outMoney的值代替第一个问号
			ps.setString(2, outType);
			ps.setString(3, outPeople);
			ps.setString(4, outDate);
			ps.setString(5, outUse);
			ps.setString(6, notes);
			//5、执行
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int delOutlayById(int id) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "DELETE FROM outlay WHERE 编号=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);//将outPeople的值代替第一个问号
			//5、执行
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		
		return 0;
	}

	@Override
	
	public int selectByOutpeople(String outPeople) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT * FROM outlay WHERE 支出者=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, outPeople);//将outPeople的值代替第一个问号
			//5、执行
		   rs = ps.executeQuery();
		   if(rs.next()){
				return rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int updateOutlay(int Id, double outMoney, String outType, String outPeople, String outDate, String outUse,
			String notes) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "UPDATE outlay SET 支出金额=?, 支出类型=?, 支出者=?, 支出日期=?, 支出用途=?, 相关备注=?  WHERE 编号=?";
			ps = conn.prepareStatement(sql);
			ps.setDouble(1, outMoney);//将outMoney的值代替第一个问号
			ps.setString(2, outType);
			ps.setString(3, outPeople);
			ps.setString(4, outDate);
			ps.setString(5, outUse);
			ps.setString(6, notes);
			ps.setInt(7, Integer.parseInt(""+Id));
			//5、执行
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		return 0;
	}


	

}
